﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Configuration;
using System.Data;
using System.Linq;
using System.Reflection;
using System.Text;
using System.Threading.Tasks;
using NB.MGJ.Common;
using System.ComponentModel.DataAnnotations.Schema;
using System.Collections;
using MySql.Data.MySqlClient;

namespace NB.MGJ.FrameWork
{


    /// =======================================================================<br/>
    /// 更新履历<br/>
    /// 序号          更新日期          责任人           更新内容<br/>
    /// 001           2017/09/19        华君            <update> subSQL +="`"+ Convert.ToString(name)+"`" + "='" + value1 + "',";
    /// 002           2017/09/19        华君            <add> fields +="`"+ Convert.ToString(name) + "`,";
    /// =======================================================================<br/>
    public class BaseExecute
    {
        /// <summary>
        /// 获取数据库连接字符串
        /// </summary>
        /// <returns></returns>
        public static string getConnStr()
        {
            string str = ConfigurationManager.ConnectionStrings["mgj_basemysql"].ToString();
            return ConfigurationManager.ConnectionStrings["mgj_basemysql"].ToString();
        }

        public BaseExecute()
        {

        }

        #region 私有方法
        public void CommandExecuteNonQuery(string sql, MySqlParameter[] parameter, Action<int> action)
        {
            // Create the Command and Parameter objects.
            MySqlConnection connection = new MySqlConnection(getConnStr());
            MySqlCommand command = new MySqlCommand(sql, connection);
            command.CommandType = CommandType.Text;
            if (parameter != null)
            {
                foreach (MySqlParameter item in parameter)
                {
                    command.Parameters.Add(item);
                }
            }
            try
            {
                connection.Open();
                int count = command.ExecuteNonQuery();
                action(count);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                connection.Close();
            }
        }



        public void CommandExecuteNonQueryReturnID(string sql, MySqlParameter[] parameter, Action<int> action)
        {
            // Create the Command and Parameter objects.
            MySqlConnection connection = new MySqlConnection(getConnStr());
            MySqlCommand command = new MySqlCommand(sql, connection);
            command.CommandType = CommandType.Text;
            if (parameter != null)
            {
                foreach (MySqlParameter item in parameter)
                {
                    command.Parameters.Add(item);
                }
            }
            try
            {
                connection.Open();
                int count = Convert.ToInt32(command.ExecuteScalar());
                action(count);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                connection.Close();
            }
        }

        public void CommandExecuteReader(string sql, MySqlParameter[] parameter, Action<MySqlDataReader> action)
        {
            // Create the Command and Parameter objects.
            MySqlConnection connection = new MySqlConnection(getConnStr());
            MySqlCommand command = new MySqlCommand(sql, connection);
            command.CommandType = CommandType.Text;
            if (parameter != null)
            {
                foreach (MySqlParameter item in parameter)
                {
                    command.Parameters.Add(item);
                }
            }
            try
            {
                connection.Open();
                MySqlDataReader reader = command.ExecuteReader();
                action(reader);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                connection.Close();
            }
        }

        /// <summary>
        /// 执行存储过程
        /// </summary>
        /// <param name="procedurename"></param>
        /// <param name="parameter">参数</param>
        /// <param name="output">是否有输出参数，如果有：输出参数的名称统一为 OutValue</param>
        /// <param name="action"></param>
        public void CommandExecuteProcedure(string procedurename, MySqlParameter[] parameter, bool output, Action<object> action)
        {
            MySqlConnection connection = new MySqlConnection(getConnStr());
            MySqlCommand command = new MySqlCommand(procedurename, connection);
            command.CommandType = CommandType.StoredProcedure;
            if (parameter != null)
            {
                foreach (MySqlParameter item in parameter)
                {
                    command.Parameters.Add(item);
                }
            }
            try
            {
                connection.Open();
                command.ExecuteNonQuery();
                if (output)
                    action(command.Parameters["@OutValue"].Value);
                else
                    action(true);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                connection.Close();
            }
        }
        public void CommandAction(string text, MySqlParameter[] parameter, bool isProcedure, Action<MySqlDataReader> action)
        {

            string _connectionString = getConnStr();
            if (string.IsNullOrWhiteSpace(_connectionString))
            {
                throw new ArgumentNullException("ConnectionString Is Null Or Empty");
            }
            using (MySqlConnection conn = new MySqlConnection(_connectionString))
            {
                using (MySqlCommand cmd = conn.CreateCommand())
                {
                    conn.Open();
                    cmd.CommandType = isProcedure ? CommandType.StoredProcedure : CommandType.Text;
                    cmd.CommandText = text;
                    if (parameter != null)
                    {
                        foreach (MySqlParameter item in parameter)
                        {
                            cmd.Parameters.Add(item);
                        }
                    }
                    MySqlDataReader reader = cmd.ExecuteReader();
                    action(reader);
                }
            }

            //// Create the Command and Parameter objects.
            //MySqlConnection connection = new MySqlConnection(getConnStr());
            //MySqlCommand command = connection.CreateCommand();// new MySqlCommand(text, connection);
            //command.CommandText = text;
            ////command.CommandTimeout=80000;//设置执行时间 2017/12/13 huajun
            //command.CommandType = isProcedure ? CommandType.StoredProcedure : CommandType.Text;
            //if (parameter != null)
            //{
            //    //command.Parameters.AddWithValue("@classid", parameter);
            //    foreach (MySqlParameter item in parameter)
            //    {
            //        command.Parameters.Add(item);
            //    }
            //}

            //try
            //{
            //    connection.Open();
            //    MySqlDataReader reader = command.ExecuteReader();
            //    action(reader);
            //    //reader.Close();
            //}
            //catch (Exception ex)
            //{
            //    throw ex;
            //}
            //finally
            //{
            //    connection.Close();
            //}
        }



        public object CommandActionScater(string text, MySqlParameter[] parameter)//, bool isProcedure, Action<SqlDataReader> action)
        {
            bool isProcedure = false;
            // Create the Command and Parameter objects.
            MySqlConnection connection = new MySqlConnection(getConnStr());
            MySqlCommand command = new MySqlCommand(text, connection);
            command.CommandType = isProcedure ? CommandType.StoredProcedure : CommandType.Text;
            if (parameter != null)
            {
                //command.Parameters.AddWithValue("@classid", parameter);
                foreach (MySqlParameter item in parameter)
                {
                    command.Parameters.Add(item);
                }
            }
            try
            {
                connection.Open();
                object reader = command.ExecuteScalar();
                return reader;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                connection.Close();
            }
        }

        /// <summary>
        /// 获取实体的插入语句
        /// </summary>
        /// <typeparam name="T">泛型</typeparam>
        /// <param name="obj">实体对象</param>
        /// <returns>返回插入语句</returns>
        public StringBuilder GetInsertSQL<T>(T obj)
        {
            Type t = obj.GetType();//获得该类的Type
            string tableKey = ""; // 主键
            string tableName = t.Name;////得到实体类名称==操作的表名
            StringBuilder strSQL = new StringBuilder();
            strSQL.Append("insert into " + tableName + "(");
            string fields = "";
            string values = "";
            foreach (PropertyInfo pi in t.GetProperties())            //再用Type.GetProperties获得PropertyInfo[]
            {
                object[] objArray = pi.GetCustomAttributes(false);
                object name = pi.Name;//用pi.GetValue获得值
                string value1 = Convert.ToString(pi.GetValue(obj, null)).Replace("'", "''");                // 替换Sql注入符
                string properName = name.ToString().ToLower();
                int tempcf = 0;
                if (objArray.Length > 0)
                {
                    //if (objArray[0}) {

                    //}
                    if ((objArray[0] as NotMappedAttribute) == null)
                    {

                        if ((objArray[0] as Property).Value == "Key")
                        {
                            tableKey = pi.Name;
                        }
                    }
                    else
                    {
                        tempcf = 1;
                    }
                }
                if (!string.IsNullOrEmpty(value1) && properName != tableKey.ToLower() && properName != BaseSet.PrimaryKey.ToLower() && properName != BaseSet.TableName.ToLower() && value1 != BaseSet.DateTimeLongNull && value1 != BaseSet.DateTimeShortNull && tempcf != 1)
                {
                    // 判断是否为空
                    if (value1 == BaseSet.NULL)
                    {
                        value1 = "";
                    }
                    fields += "`" + Convert.ToString(name) + "`,";
                    values += "'" + value1 + "',";
                }
            }
            // 去掉最后一个,
            fields = fields.TrimEnd(',');
            values = values.TrimEnd(',');
            // 拼接Sql串
            strSQL.Append(fields);
            strSQL.Append(") values (");
            strSQL.Append(values);
            strSQL.Append(")");
            strSQL.Append(";SELECT @@IDENTITY;");
            return strSQL;
        }

        /// <summary>
        /// 获取实体的更新SQL串
        /// </summary>
        /// <typeparam name="T">泛型</typeparam>
        /// <param name="obj">实体对象</param>
        /// <returns>返回插入语句</returns>
        public StringBuilder GetUpdateSQL<T>(T obj)
        {
            Type t = obj.GetType();//获得该类的Type
            string tableKey = "";
            string keyValue = "";
            string tableName = t.Name;
            StringBuilder strSQL = new StringBuilder();
            //if (string.IsNullOrEmpty(keyValue))
            //{
            //    return strSQL;
            //}
            strSQL.Append("update " + tableName + " set ");
            string subSQL = "";
            //再用Type.GetProperties获得PropertyInfo[]
            foreach (PropertyInfo pi in t.GetProperties())
            {
                object[] objArray = pi.GetCustomAttributes(false);
                object name = pi.Name;//用pi.GetValue获得值
                string value1 = Convert.ToString(pi.GetValue(obj, null)).Replace("'", "''");// 替换Sql注入符
                //if (pi.PropertyType.FullName == "System.String"&&value1=="")
                //{
                //   value1=" ";
                //}


                string properName = name.ToString().ToLower();
                int tempcf = 0;
                if (objArray.Length > 0)//如果字段上面有类型的话
                {
                    if ((objArray[0] as NotMappedAttribute) == null)//判断不存在数据库的字段
                    {

                        if ((objArray[0] as Property).Value == "Key")
                        {
                            tableKey = pi.Name;
                            keyValue = value1;
                        }
                    }
                    else
                    {
                        tempcf = 1;
                    }
                }

                if (!string.IsNullOrEmpty(value1) && properName != tableKey.ToLower() && properName != BaseSet.PrimaryKey.ToLower() && properName != BaseSet.TableName.ToLower() && value1 != BaseSet.DateTimeLongNull && value1 != BaseSet.DateTimeShortNull && tempcf != 1)
                {
                    if (value1 == BaseSet.NULL)
                    {
                        value1 = "";
                    }
                    subSQL += "`" + Convert.ToString(name) + "`" + "='" + value1 + "',";
                }

            }
            subSQL = subSQL.TrimEnd(',');// 去掉最后一个,
            strSQL.Append(subSQL);// 拼接上更新子句
            string condition = " where " + tableKey + "='" + keyValue.Replace("'", "''") + "'";
            strSQL.Append(condition);  // 加入更新条件
            return strSQL;
        }

        public object ReaderObject<T>(MySqlDataReader reader)
        {
            object result = null;
            if (typeof(bool) == typeof(T))
            {
                result = ReaderElement(reader, 0, typeof(bool));
            }
            else if (typeof(byte) == typeof(T))
            {
                result = ReaderElement(reader, 0, typeof(byte));
            }
            else if (typeof(char) == typeof(T))
            {
                result = ReaderElement(reader, 0, typeof(char));
            }
            else if (typeof(DateTime) == typeof(T) || typeof(DateTime?) == typeof(T))
            {
                result = ReaderElement(reader, 0, typeof(DateTimeOffset));
            }
            else if (typeof(decimal) == typeof(T))
            {
                result = ReaderElement(reader, 0, typeof(decimal));
            }
            else if (typeof(double) == typeof(T))
            {
                result = ReaderElement(reader, 0, typeof(double));
            }
            else if (typeof(float) == typeof(T))
            {
                result = ReaderElement(reader, 0, typeof(float));
            }
            else if (typeof(Guid) == typeof(T))
            {
                result = ReaderElement(reader, 0, typeof(Guid));
            }
            else if (typeof(short) == typeof(T))
            {
                result = ReaderElement(reader, 0, typeof(short));
            }
            else if (typeof(int) == typeof(T))
            {
                result = ReaderElement(reader, 0, typeof(int));
            }
            else if (typeof(string) == typeof(T))
            {
                result = ReaderElement(reader, 0, typeof(string));
            }
            else
            {
                result = System.Activator.CreateInstance<T>();
                int i = 0;
                foreach (PropertyDescriptor property in TypeDescriptor.GetProperties(result))
                {
                    try
                    {
                        if (reader.GetSchemaTable().Select("ColumnName='" + property.DisplayName + "'").Length > 0)
                        {
                            i = reader.GetOrdinal(property.DisplayName);
                            if (property.PropertyType.Name == "Int32")
                            {
                                int o = Convert.ToInt32(i);
                                property.SetValue(result, ReaderElement(reader, o, property.PropertyType));
                            }
                            else
                            {
                                property.SetValue(result, ReaderElement(reader, i, property.PropertyType));
                            }
                          
                        }
                    }
                    catch (Exception ex)
                    {
                        throw new ArgumentException("绑定属性【" + property.Name + "】发生异常:" + ex.Message);
                    }
                }
            }
            return result;
        }

        public object ReaderElement(MySqlDataReader reader, int count, Type type)
        {
            if (typeof(bool) == type)
            {
                return reader.GetBoolean(count);
            }
            else if (typeof(byte) == type)
            {
                return reader.GetByte(count);
            }
            else if (typeof(char) == type)
            {
                return reader.GetChar(count);
            }
            else if (typeof(DateTime) == type || typeof(DateTime?) == type)
            {
                object ra = reader[count];
                if (ra is DBNull)
                {
                    return new DateTime?();
                }
                return ra;
                //return reader.GetDateTime(count);
            }
            //else if (typeof(DateTimeOffset) == type)
            //{
            //    return reader.GetDateTimeOffset(count);
            //}
            else if (typeof(decimal) == type || typeof(int?) == type)
            {
                object ra = reader[count];
                if (ra is DBNull)
                {
                    return new decimal?();
                }
                return ra;
                //return reader.GetDecimal(count);
            }
            else if (typeof(double) == type)
            {
                return reader.GetDouble(count);
            }
            else if (typeof(float) == type)
            {
                return reader.GetFloat(count);
            }
            else if (typeof(Guid) == type)
            {
                return reader.GetGuid(count);
            }
            else if (typeof(short) == type)
            {
                return reader.GetInt16(count);
            }
            else if (typeof(int) == type || typeof(int?) == type)
            {
                //return reader.GetInt32(count);
                object ra = reader[count];
                if (ra is DBNull)
                {
                    return new int?();
                }
                return ra;
            }
            else if (typeof(long) == type)
            {
                object ra = reader[count];
                if (ra is DBNull)
                {
                    return new long?();
                }
                return Convert.ToInt64(ra);
            }
            else
            {
                return reader.IsDBNull(count) ? null : reader.GetString(count);
            }
        }
        #endregion


        #region Dy
        public void CommandExecuteNonQuery(string sql, MySqlParameter[] parameter, Action<int> action, string connstr)
        {
            // Create the Command and Parameter objects.
            MySqlConnection connection = new MySqlConnection(connstr);
            MySqlCommand command = new MySqlCommand(sql, connection);
            command.CommandType = CommandType.Text;
            if (parameter != null)
            {
                foreach (MySqlParameter item in parameter)
                {
                    command.Parameters.Add(item);
                }
            }
            try
            {
                connection.Open();
                int count = command.ExecuteNonQuery();
                action(count);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                connection.Close();
            }
        }

        /// <summary>
        /// 执行一条计算查询结果语句，返回查询结果（object）。
        /// </summary>
        /// <param name="SQLString">计算查询结果语句</param>
        /// <returns>查询结果（object）</returns>
        public object GetSingle(string SQLString)
        {
            using (MySqlConnection connection = new MySqlConnection(getConnStr()))
            {
                using (MySqlCommand cmd = new MySqlCommand(SQLString, connection))
                {
                    try
                    {
                        connection.Open();
                        object obj = cmd.ExecuteScalar();
                        if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
                        {
                            return null;
                        }
                        else
                        {
                            return obj;
                        }
                    }
                    catch (System.Data.SqlClient.SqlException e)
                    {
                        connection.Close();
                        throw e;
                    }
                    finally
                    {
                        connection.Close();
                    }
                }
            }
        }

        public DataTable GetDataTableDigitcode(string _filed, string _tbname, string connectionString)
        {
            if (!string.IsNullOrWhiteSpace(connectionString) && !string.IsNullOrWhiteSpace(_filed) && !string.IsNullOrWhiteSpace(_tbname))
            {
                using (MySqlConnection conn = new MySqlConnection(connectionString))
                {
                    using (MySqlCommand cmd = conn.CreateCommand())
                    {
                        conn.Open();
                        cmd.CommandType = CommandType.Text;
                        cmd.CommandText = string.Format("SELECT COUNT(1) FROM {0}", _tbname);
                        object result = cmd.ExecuteScalar();
                        int _Count = 0;
                        if (result != null)
                        {
                            _Count = Convert.ToInt32(result);
                        }
                        DataTable tb = new DataTable();
                        int _L_Id = 0;
                        _filed += "ID ";
                        while (_Count >= 10000)
                        {
                            cmd.CommandText = string.Format("SELECT {0} FROM {1} WHERE ID >{2} LIMIT 10000", _filed, _tbname, _L_Id);
                            MySqlDataAdapter adapter = new MySqlDataAdapter(cmd);
                            adapter.FillAsync(tb);
                            _Count -= 10000;
                            object rs = tb.Rows[tb.Rows.Count - 1]["ID"];
                            _L_Id = Convert.ToInt32(rs);
                        }
                        if (_Count > 0)
                        {
                            if (tb.Rows.Count > 0)
                            {
                                object rs = tb.Rows[tb.Rows.Count - 1]["ID"];
                                _L_Id = Convert.ToInt32(rs);
                            }
                            cmd.CommandText = string.Format("SELECT {0} FROM {1} WHERE ID >{2} LIMIT 10000", _filed, _tbname, _L_Id);
                            MySqlDataAdapter adapter = new MySqlDataAdapter(cmd);
                            adapter.Fill(tb);
                        }
                        return tb;
                    }
                }
            }
            return null;
        }

        public DataTable GetDataTableDigitcode(string SQLString, string connstr)
        {
            using (MySqlConnection connection = new MySqlConnection(connstr))
            {
                using (MySqlCommand cmd = new MySqlCommand(SQLString, connection))
                {
                    try
                    {
                        DataTable table = new DataTable();
                        connection.Open();
                        MySqlDataAdapter adapter = new MySqlDataAdapter(cmd);
                        adapter.Fill(table);
                        return table;

                    }
                    catch (System.Data.SqlClient.SqlException e)
                    {
                        connection.Close();
                        throw e;
                    }
                    finally
                    {
                        connection.Close();
                    }
                }
            }
        }

        /// <summary>
        /// 根据SQL查询 DataTable
        /// </summary>
        /// <param name="SQLString"></param>
        /// <returns></returns>
        public DataTable GetDataTable(string SQLString)
        {
            using (MySqlConnection connection = new MySqlConnection(getConnStr()))
            {
                using (MySqlCommand cmd = new MySqlCommand(SQLString, connection))
                {
                    try
                    {
                        DataTable table = new DataTable();
                        connection.Open();
                        MySqlDataAdapter adapter = new MySqlDataAdapter(cmd);
                        adapter.Fill(table);
                        return table;

                    }
                    catch (System.Data.SqlClient.SqlException e)
                    {
                        connection.Close();
                        throw e;
                    }
                    finally
                    {
                        connection.Close();
                    }
                }
            }
        }


        /// <summary>
        /// 执行多条SQL语句，实现数据库事务。
        /// </summary>
        /// <param name="SQLStringList">SQL语句的哈希表（key为sql语句，value是该语句的SqlParameter[]）</param>
        public void ExecuteSqlTran(Hashtable SQLStringList)
        {
            using (MySqlConnection conn = new MySqlConnection(getConnStr()))
            {
                conn.Open();
                using (MySqlTransaction trans = conn.BeginTransaction())
                {
                    MySqlCommand cmd = new MySqlCommand();
                    try
                    {
                        //循环
                        foreach (DictionaryEntry myDE in SQLStringList)
                        {
                            string cmdText = myDE.Key.ToString();
                            MySqlParameter[] cmdParms = (MySqlParameter[])myDE.Value;
                            PrepareCommand(cmd, conn, trans, cmdText, cmdParms);
                            int val = cmd.ExecuteNonQuery();
                            cmd.Parameters.Clear();
                        }
                        trans.Commit();
                    }
                    catch
                    {
                        trans.Rollback();
                        throw;
                    }
                }
            }
        }


        private static void PrepareCommand(MySqlCommand cmd, MySqlConnection conn, MySqlTransaction trans, string cmdText, MySqlParameter[] cmdParms)
        {
            if (conn.State != ConnectionState.Open)
                conn.Open();
            cmd.Connection = conn;
            cmd.CommandText = cmdText;
            if (trans != null)
                cmd.Transaction = trans;
            cmd.CommandType = CommandType.Text;//cmdType;
            if (cmdParms != null)
            {


                foreach (MySqlParameter parameter in cmdParms)
                {
                    if ((parameter.Direction == ParameterDirection.InputOutput || parameter.Direction == ParameterDirection.Input) &&
                        (parameter.Value == null))
                    {
                        parameter.Value = DBNull.Value;
                    }
                    cmd.Parameters.Add(parameter);
                }
            }
        }

        /// <summary>
        /// 执行
        /// </summary>
        /// <returns></returns>
        public long insertRturnIDSql(string sql, MySqlParameter[] parameter) 
        {
            // Create the Command and Parameter objects.
            MySqlConnection connection = new MySqlConnection(getConnStr());
            MySqlCommand command = new MySqlCommand(sql, connection);
            command.CommandType = CommandType.Text;
            if (parameter != null)
            {
                foreach (MySqlParameter item in parameter)
                {
                    command.Parameters.Add(item);
                }
            }
            try
            {
                connection.Open();
                int count = command.ExecuteNonQuery();
                return command.LastInsertedId;  
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                connection.Close();
            }
        }
        #endregion
    }
}
